/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package putime.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import putime.db.DBModel;
import putime.newspaperagent.product.Product;
import putime.newspaperagent.product.ProductType;
import putime.newspaperagent.supplier.Supplier;

/**
 *
 * @author My PC
 */
public class ModelProduct extends DBModel {

    Vector<Product> vProduct;
    Vector<ProductType> vType;

    public Vector initType() {
        vType = new Vector<>();
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_producttype");
            rs = prst.executeQuery();
            while (rs.next()) {
                ProductType type = new ProductType(rs.getInt(1), rs.getString(2));
                vType.add(type);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnect();
        return vType;
    }
    
//    public ProductType getaType(String chon){
//        ProductType tam=null;
//        try {
//            prst = conn.prepareStatement("select * from tbl_producttype where Name = ?");
//            prst.setString(1, chon);
//            rs = prst.executeQuery();
//            rs.next();
//            tam = new ProductType(rs.getInt(1), rs.getString(2));
//        } catch (SQLException ex) {
//            Logger.getLogger(ModelProduct.class.getName()).log(Level.SEVERE, null, ex);
//        }
//        return tam;
//    }

    public Vector<Product> initCobProd(String typeName, String lang) {
        vProduct = new Vector<>();
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_products a inner join tbl_producttype b on a.TypeID = b.TypeID where b.Name = ? and a.Language = ?");
            prst.setString(1, typeName);
            prst.setString(2, lang);
            rs = prst.executeQuery();
            while (rs.next()) {
                Product product = new Product(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getInt(7));
                vProduct.add(product);
            }
        } catch (SQLException e) {
            Logger.getLogger(ModelProduct.class.getName()).log(Level.SEVERE, null, e);
        }
        closeConnect();
        return vProduct;
    }

    public Product ComboChange(String chon) {
        openConnect();
        Product s = null;
        try {
            prst = conn.prepareStatement("select * from tbl_products where ProID = ?");
            prst.setString(1, chon);
            rs = prst.executeQuery();
            rs.next();
            s = new Product(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getInt(7));
            closeConnect();
            return s;
        } catch (SQLException ex) {
            closeConnect();
            s = new Product("","",1,1,"","",0);
            return s;
        }
        
    }

    public boolean deleteProduct(String chon) {
        openConnect();
        try {
            prst = conn.prepareStatement("delete from tbl_products where ProID = ?");
            prst.setString(1, chon);
            prst.executeUpdate();
            closeConnect();
            return true;
        } catch (SQLException ex) {
            closeConnect();
            return false;
        }
    }
     
    public Vector<Product> findID(String chon) {
        Vector<Product> vProduct = new Vector<>();
        Product product;
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_products where ProID LIKE '%" + chon + "%'");
            rs = prst.executeQuery();
            while (rs.next()) {
                product = new Product(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getInt(7));
                vProduct.add(product);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelSupplier.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return vProduct;
    }

    public Vector<Product> findName(String chon) {
        Vector<Product> vProduct = new Vector<>();
        Product product;
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_products where Name LIKE '%" + chon + "%'");
            rs = prst.executeQuery();
            while (rs.next()) {
                product = new Product(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getInt(7));
                vProduct.add(product);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelSupplier.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
         return vProduct;
    }

    public boolean editProduct(String chon, String name, int supID, String description, String lang, int Status) {
        openConnect();
        try {
            prst = conn.prepareStatement("update tbl_products set Name = ?, SupID = ?, Description = ?, Language = ? ,Status = ? where ProID = ?");
            prst.setString(1, name);
            prst.setInt(2, supID);
            prst.setString(3, description);
            prst.setString(4, lang);
            prst.setInt(5, Status);
            prst.setString(6, chon);
            prst.executeUpdate();
            closeConnect();
            return true;
        } catch (SQLException ex) {
            closeConnect();
            return false;
        }
    }

    public String checkProID(int typeID) {
        openConnect();
        String id;
        try {
            prst = conn.prepareStatement("select count(*) from tbl_products where TypeID = ?");
            prst.setInt(1, typeID);
            rs = prst.executeQuery();
            rs.next();
            int check = rs.getInt(1);
            if (check == 0) {
                id = "0001";
            } else {
                check++;
                if (check < 10) {
                    id = "000" + check;
                } else if (check < 100) {
                    id = "00" + check;
                } else if (check < 1000) {
                    id = "0" + check;
                } else {
                    id = "" + check;
                }
            }
            prst = conn.prepareStatement("select * from tbl_producttype where TypeID = ?");
            prst.setInt(1, typeID);
            rs = prst.executeQuery();
            rs.next();
            String pre_name = rs.getString(2);
            closeConnect();
            pre_name = String.valueOf(pre_name.charAt(0));
            return pre_name + id;
        } catch (SQLException ex) {
            closeConnect();
            return "Undefine";
        }

    }

    public boolean addProduct(String name, int supID, int typeID, String description, String lang) {
        String proID = checkProID(typeID);
        openConnect();
        try {
            prst = conn.prepareStatement("insert tbl_products(ProID,Name,SupID,TypeID,Description,Language,Status) values (?,?,?,?,?,?,1)");
            prst.setString(1, proID);
            prst.setString(2, name);
            prst.setInt(3, supID);
            prst.setInt(4, typeID);
            prst.setString(5, description);
            prst.setString(6, lang);
            prst.executeUpdate();
            closeConnect();
            return true;
        } catch (SQLException ex) {
            closeConnect();
            return false;
        }
    }
}
